<?php
class Page_statistics extends V_View {
	// ------------------------------------------------------------------------------------------
    // ----------Главная страница ----------- ---------------------------------------------------
    // ------------------------------------------------------------------------------------------
    function index () {
    	$this->assign('page_title','Статистика');
    	$this->display('header.tpl');
        $this->display('pages/statistics/index.tpl');
        $this->display('footer.tpl');
    }
    function month () {
    	for ($i=1;$i<32;$i++) $date_list[$i]=0;
    	// Получение добаленных листов
    	$date_res=V_DB::fetchAll(
    		'SELECT DAY(list_add_date) AS date '.
            'FROM v_work_list '.
            'WHERE YEAR(NOW())=YEAR(list_add_date) and MONTH(NOW())=MONTH(list_add_date)'
    	);
    	if (!empty($date_res)) 
    		foreach ($date_res as $date_item)
    			$date_list[$date_item['date']]++;
    	// Получение информации о категориях
    	$cat_res = V_DB::fetchAll(
    		'SELECT id, category_name FROM v_category_list '
    	);
    	if (!empty($cat_res))
    		foreach ($cat_res as $cat_item) {
    			$category_array['name']=$cat_item['category_name'];
    			$category_array['count']=V_DB::count(
    				'v_work_list',
    				'client_category_id='.$cat_item['id'].
            		' AND YEAR(NOW())=YEAR(list_add_date) and MONTH(NOW())=MONTH(list_add_date)'
    			);
    			$category_list[]=$category_array;
    		}
    	// Получение количества смс у городов
    	$sms_res=V_DB::fetchAll('SELECT id, city, sms_count FROM v_work_list_city');
    	if (!empty($sms_res)) { 
    		foreach ($sms_res as $sms_item) {
    			$sms_list[]=$sms_item['sms_count'];
    			$sms_city[]=$sms_item['city'];
    			$sms_client_res=V_DB::fetchAll(
    				'SELECT sms_count, sms_money FROM v_sms_list_client WHERE send_city_id='.$sms_item['id'].
    				' AND YEAR(NOW())=YEAR(send_date) and MONTH(NOW())=MONTH(send_date)'.
    				' ORDER BY send_city_id');
    			if (!empty($sms_client_res)) {
    				$cl=0; $ml=0;
    				foreach ($sms_client_res as $sms_client_item) {
    					$cl+=$sms_client_item['sms_count'];
    					$ml+=$sms_client_item['sms_money']*$sms_client_item['sms_count'];
    				}
    				$sms_client[]=$cl;
    				$ml>0 ? $sms_price[]=round($ml / $cl,1) : $sms_price[]=0;
    			} else { $sms_client[]=0;$sms_price[]=0; }
    		}
    	}
    	// Получение информации о городах
        $city_list=V_DB::fetchAll('SELECT id, city FROM v_work_list_city');
    	// Получение информации о качестве
    	$state_res=V_DB::fetchAll("SELECT sl.service_state_id, wl.list_is_sign ".
            				'FROM v_work_list_services AS sl '.
    						'LEFT JOIN v_work_list AS wl ON wl.id=sl.list_id '.
    						'WHERE YEAR(NOW())=YEAR(list_add_date) and MONTH(NOW())=MONTH(list_add_date)'
    	);
    	if (!empty($state_res)) {
    		$state_list[0]=Array('переговоры',0);
    		$state_list[1]=Array('подписано',0);
    		$state_list[2]=Array('отказ',0); 
    		$state_list[3]=Array('с конкурентом',0);
    		foreach ($state_res as $state_item) {
    			switch ($state_item['service_state_id']) {
    				case 0 : switch ($state_item['list_is_sign'])  {
    							case 0: $state_list[0][1]++; break;
    							case 1: $state_list[1][1]++; break;
    						} break;
    				case 1 : $state_list[2][1]++; break;
    				case 2 : $state_list[3][1]++; break;
    			}
    		}
    	}
    	// Получение количества листов
    	$wl_res=V_DB::fetchAll('SELECT wl.id FROM v_work_list_services AS sl '.
    						'LEFT JOIN v_work_list AS wl ON wl.id=sl.list_id '.
    						'WHERE YEAR(NOW())=YEAR(wl.list_add_date) and MONTH(NOW())=MONTH(wl.list_add_date)'
    	);
    	if (!empty($wl_res)) $wl_count=sizeof($wl_res);
    	
    	$this->assign ('date_list',$date_list);
    	$this->assign ('sms_list',$sms_list);
    	$this->assign ('city_list',$city_list);
    	$this->assign ('sms_city',$sms_city);
    	$this->assign ('sms_client',$sms_client);
    	$this->assign ('sms_price',$sms_price);
    	$this->assign ('sms_city_count',sizeof($city_list)-1);
    	$this->assign ('state_list',$state_list);
    	$this->assign ('category_list',$category_list);
    	$this->assign ('category_count',sizeof($category_list)-1);
    	$this->assign ('wl_count',$wl_count);	
    	$this->display('pages/statistics/month.tpl');
    }
	function year () {
    	// Получение добаленных листов
    	$date_res=V_DB::fetchAll(
    		'SELECT YEAR(list_add_date) AS year,  MONTH(list_add_date) AS month '.
            'FROM v_work_list ORDER by list_add_date');
    	if (!empty($date_res)) {
    		foreach ($date_res as $date_item) {
    			if (!empty (
    				$date_list[$date_item['year'].'.'.$date_item['month']]
    			)) $date_list[$date_item['year'].'.'.$date_item['month']]++;
    			else $date_list[$date_item['year'].'.'.$date_item['month']]=1;
    		}
    	}
    	// Получение информации о категориях
    	$cat_res=V_DB::query(
    		'SELECT id, category_name FROM v_category_list ');
    	if (!empty($cat_res)) { 
    		foreach ($cat_res as $cat_item) {
    			$category_array['name']=$cat_item['category_name'];
    			$category_array['count']=V_DB::count(
    				'v_work_list','client_category_id='.$cat_item['id']
    			);
    			$category_list[]=$category_array;
    		}
    	}
    	$sms_res=V_DB::fetchAll('SELECT id, city, sms_count FROM v_work_list_city');
    	if (!empty($sms_res)) { 
    		foreach ($sms_res as $sms_item) {
    			$sms_list[]=$sms_item['sms_count'];
    			$sms_city[]=$sms_item['city'];
    			$sms_client_res=V_DB::fetchAll(
    				'SELECT sms_count, sms_money FROM v_sms_list_client WHERE send_city_id='.
    				$sms_item['id'].' ORDER BY send_city_id');
				if (!empty($sms_client_res)) {
    				$cl=0; $ml=0;
    				foreach ($sms_client_res as $sms_client_item) {
    					$cl+=$sms_client_item['sms_count'];
    					$ml+=$sms_client_item['sms_money']*$sms_client_item['sms_count'];
    				}
    				$sms_client[]=$cl;
    				$ml>0 ? $sms_price[]=round($ml / $cl,1) : $sms_price[]=0;
    			} else { $sms_client[]=0;$sms_price[]=0; }
    		}
    	}
    	// Получение информации о городах
        $city_list=V_DB::fetchAll('SELECT id, city FROM v_work_list_city');
    	// Получение  информации о качестве
    	$state_res=V_DB::fetchAll("SELECT sl.service_state_id, wl.list_is_sign ".
            					'FROM v_work_list_services AS sl '.
    							'LEFT JOIN v_work_list AS wl ON wl.id=sl.list_id '
    	);
	    if (!empty($state_res)) {
    		$state_list[0]=Array('переговоры',0);
    		$state_list[1]=Array('подписано',0);
    		$state_list[2]=Array('отказ',0); 
    		$state_list[3]=Array('с конкурентом',0);
    		foreach ($state_res as $state_item) {
    			switch ($state_item['service_state_id']) {
    				case 0 : switch ($state_item['list_is_sign'])  {
    							case 0: $state_list[0][1]++; break;
    							case 1: $state_list[1][1]++; break;
    						} break;
    				case 1 : $state_list[2][1]++; break;
    				case 2 : $state_list[3][1]++; break;
    			}
    		}
    	}
    	// Получение количества листов
    	$wl_count=V_DB::count('v_work_list_services ');

    	$this->assign ('date_list',$date_list);
    	$this->assign ('sms_list',$sms_list);
    	$this->assign ('city_list',$city_list);
    	$this->assign ('sms_city',$sms_city);
    	$this->assign ('sms_client',$sms_client);
    	$this->assign ('sms_price',$sms_price);
    	$this->assign ('sms_city_count',sizeof($city_list)-1);
    	$this->assign ('state_list',$state_list);
    	$this->assign ('category_list',$category_list);
    	$this->assign ('category_count',sizeof($category_list)-1);
    	$this->assign ('wl_count',$wl_count);	
    	$this->display('pages/statistics/year.tpl');
    }
}